package map;

import java.sql.*;
import java.util.ArrayList;


public class Services {

	private Connection conn = null;
	private Statement s = null;
	private ResultSet rs = null, rs1 = null;
	private static String db_port = "5432";
	private static String db_user = "postgres";
	private static String db_pass = "admin";

	private void openConnection(String server, String database)
			throws SQLException, ClassNotFoundException {
		if (conn == null) {
			Class.forName("org.postgresql.Driver");
			// String url = "jdbc:postgresql://localhost:5432/postgis";
			String url = "jdbc:postgresql://" + server + ":" + db_port + "/"
					+ database;
			conn = DriverManager.getConnection(url, db_user, db_pass);
			s = conn.createStatement();
			System.out.println("Database is opened!");
		}
	}

	private void closeConnection() throws SQLException {
		if (!conn.isClosed()) {
			conn.close();
			System.out.println("Database is closed!");
		}
	}

	public String isLogin(String username, String password, String server,
			String database) throws ClassNotFoundException,
			SQLException {
		this.openConnection(server, database);
		s = conn.createStatement();
		String sql = "Select * From USERS WHERE ID = '" + username + "'"
				+ " And password = '" + password + "'";
		rs = s.executeQuery(sql);

		if (rs.next()) {
			// Kiem tra xem co thuoc so nganh hien tai hay khong?
			String masonganh = rs.getString(4);
			System.out.println("So nganh: " + masonganh);
			String sql1 = "Select * From SONGANH Where URL = '" + server
					+ "'";
			rs1 = s.executeQuery(sql1);
			while (rs1.next()) {
				if (rs1.getString(1).equals(masonganh)) {
					//this.closeConnection();
					return "1";
				}
			}
			//this.closeConnection();
			return "2";
		}
		//this.closeConnection();
		return "3";
	}

	// Tra ve danh sach cac layers cua so nghanh tuong ung
	public ArrayList getLayerList(String server, String database, String username, String password) throws SQLException, ClassNotFoundException {
		String usertype = "3";
		ArrayList layer_list = new ArrayList();	
		String tenlop = " ";
		String chiase = " ";
		//Kiem tra usertype cua user hien tai
		
		usertype = this.isLogin(username, password, server, database);
		
		this.openConnection(server,database);
		
		String sql = "Select * From DSLOP Where chiase >= " + usertype;
		rs = s.executeQuery(sql);

		while (rs.next()) {
			String[] arr = new String[2];
			if (rs.getString("tenlop") == null) {
				tenlop = " ";				
			} else {
				tenlop = rs.getString("tenlop");				
			}
			if (rs.getString("chiase") == null) {
				chiase = " ";
			} else {
				chiase = rs.getString("chiase");
			}
			System.out.println(tenlop);
			arr[0] = tenlop;
			arr[1] = chiase;
			//arr[2] = the_geom;
			layer_list.add(arr);
		}
		this.closeConnection();
		return layer_list;
	}


	public ArrayList getLayerByName(String layer_name, String server, String database) throws SQLException,
				ClassNotFoundException {		
			this.openConnection(server, database);
			ArrayList layer_list = new ArrayList();			
			String sql = "Select ST_Astext(geom) As geom From " + layer_name;
				
			rs = s.executeQuery(sql);
			ResultSetMetaData rs_meta = rs.getMetaData();
			int numOfCol = rs_meta.getColumnCount();			
			//lap qua tung dong trong ResultSet
			String nameOfCol = "";
			String valueOfCol = "";	
			//Gan ten cua lop tuong ung vao item dau tien cua mang
			String[] arr_name = new String[numOfCol];
			arr_name[0] = layer_name;
			layer_list.add(0, arr_name);
			//do du lieu cua lop tuong ung vao mang
			while (rs.next()){
				String[] arr = new String[numOfCol];
				//lap qua tung column
				for (int i = 1; i <= numOfCol; i++){
					nameOfCol = rs_meta.getColumnName(i);
					valueOfCol = rs.getString(nameOfCol);
					if (valueOfCol == null){
						valueOfCol = " ";
					}
					//Them vao mang
					arr[i - 1] = valueOfCol;
				}
				layer_list.add(arr);
			}		
			
			this.closeConnection();
			return layer_list;
	}
	
	public ArrayList getSoNganh(String server, String database) throws SQLException, ClassNotFoundException{
		this.openConnection(server, database);
		ArrayList songanh_list = new ArrayList();			
		String sql = "Select * From SONGANH";			
		rs = s.executeQuery(sql);
		String masn = "";
		String tensn = "";
		String url = "";
		while (rs.next()){
			String[] arr = new String[3];
			if (rs.getString("masn") == null) {
				masn = " ";				
			} else {
				masn = rs.getString("masn");				
			}
			if (rs.getString("tensn") == null) {
				tensn = " ";
			} else {
				tensn = rs.getString("tensn");
			}
			if(rs.getString("url") == null){
				url = " ";
			}else{
				
				String t = rs.getString("url");
				//Cat bo port
				url = t.split(":")[0];				
			}
			
			arr[0] = masn;
			arr[1] = tensn;
			arr[2] = url;
			//arr[2] = the_geom;
			songanh_list.add(arr);
		}
		this.closeConnection();
		return songanh_list;
	}
	
	public static void main(String[] args) throws ClassNotFoundException,
			SQLException, CloneNotSupportedException {
		String server = "172.16.96.68";
		String database = "postgis20";
		Services obj = new Services();
		//obj.openConnection(server, database);
		//System.out.println("Result: "+obj.isLogin("00001", "00001", server, database));
		obj.getLayerList(server, database, "00001", "00001");
	}

}
